3. Exploratory Data Analysis¶
Exploratory data analysis (EDA) involves taking a first look at a dataset and summarising its salient characteristics using tables and graphics. It is (or should be) the stage before testing hypotheses and can be useful in informing hypotheses. In this chapter, we’ll look at a few options for EDA using code.
To show how to do EDA using code, we will need a dataset to explore. We’ll use the Grinell housing dataset, which covers houses sold between 2005 and 2015 in Grinnell, Iowa. It was assembled by a local estate agent, Matt Karjalahti.
First, let’s import a couple of essential packages:
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from itertools import islice
# Plot settings
plt.style.use(
"https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt"
)
3.1. Preliminaries: extracting, typing, and initial cleaning¶
Let’s grab some data to do exploratory data analysis on.
df = pd.read_csv(
"https://vincentarelbundock.github.io/Rdatasets/csv/Stat2Data/GrinnellHouses.csv",
index_col=0,
)
df.head()
| Date | Address | Bedrooms | Baths | SquareFeet | LotSize | YearBuilt | YearSold | MonthSold | DaySold | CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 16695 | 1510 First Ave #112 | 2 | 1.0 | 1120.0 | NaN | 1993 | 2005 | 9 | 16 | 6.25 | 17000 | 10500 | 7000 | 66.67 |
| 2 | 16880 | 1020 Center St | 3 | 1.0 | 1224.0 | 0.172176 | 1900 | 2006 | 3 | 20 | 22.06 | 35000 | 35000 | 27000 | 77.14 |
| 3 | 16875 | 918 Chatterton St | 4 | 1.0 | 1540.0 | NaN | 1970 | 2006 | 3 | 15 | 18.18 | 54000 | 47000 | 28000 | 59.57 |
| 4 | 16833 | 1023 & 1025 Spring St. | 3 | 1.0 | 1154.0 | NaN | 1900 | 2006 | 2 | 1 | 26.00 | 65000 | 49000 | 30000 | 61.22 |
| 5 | 16667 | 503 2nd Ave | 3 | 1.0 | 1277.0 | 0.206612 | 1900 | 2005 | 8 | 19 | 24.08 | 35000 | 35000 | 30750 | 87.86 |
There are some things we should do even before an exploratory data analysis; we should make sure we’ve cast the columns to the right types and converted any that need converting. Even a quick look at the data provided by .head() shows that the Date column is in a weird format. It also seems like the number of Baths column has some data issues because it is a floating point number rather than an integer. However, this a North American quirk: realtors refer to bathrooms with only a toilet and sink as a “half bath” (and sometimes those with a shower but no bathtub as a “three-quarter bath,” which also shows up in the data). So this is okay, and we mainly need concern ourselves with the Date column.
Fortunately, there is guidance attached to the dataset as to what the Date column really means: it’s just a counter of days that encodes the date of sale that begins with 1st Jan 2005=16436. The documents also tell us that the final year is 2015. We’d really like to have a more usable datetime column than this so let’s sort this out first. Ideally, we’d like to map the Date column into a new datetime column that turns 16436 into 1st Jan 2005, 16437 into 2nd Jan 2005, and so on, until we have a mapping for every date between the first date in the dataset and the last.
Our solution will be to create a function that maps the code for Date into a genuine datetime using the pd.DateOffset function and apply it to the Date column using apply:
def convert_date_code_to_datetime(date):
start_code = 16436
return pd.to_datetime("01-01-2005") + pd.DateOffset(days=date - start_code)
df["datetime"] = df["Date"].apply(convert_date_code_to_datetime)
# Check the 'biggest' rows by datetime
df.nlargest(5, columns=["datetime"])
| Date | Address | Bedrooms | Baths | SquareFeet | LotSize | YearBuilt | YearSold | MonthSold | DaySold | CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | datetime | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 915 | 20186 | 957 350th Ave | 3 | 2.00 | 1700.0 | 11.430000 | 1966 | 2015 | 4 | 8 | 128.82 | 229000 | 229000 | 219000 | 95.63 | 2015-04-09 |
| 886 | 20185 | 232 4th Ave W | 3 | 2.50 | NaN | 0.139991 | 2004 | 2015 | 4 | 7 | 0.00 | 137500 | 137500 | 128500 | 93.45 | 2015-04-08 |
| 905 | 20185 | 1726 Summer St | 4 | 1.75 | 1512.0 | 0.530000 | 1996 | 2015 | 4 | 7 | 125.00 | 239900 | 219900 | 189000 | 85.95 | 2015-04-08 |
| 920 | 20184 | 108 East St | 4 | 3.00 | 1650.0 | 0.320000 | 2013 | 2015 | 4 | 6 | 156.97 | 259000 | 259000 | 259000 | 100.00 | 2015-04-07 |
| 834 | 20181 | 1405 Prince St | 3 | 1.50 | 1094.0 | NaN | 1900 | 2015 | 4 | 3 | 18.28 | 32500 | 32500 | 20000 | 61.54 | 2015-04-04 |
An extra column containing datetime has been added and, looking at the max rows, we can see that it does indeed run all the way to 2015 as expected from the documentation of the dataset.
Okay, now we want to sort out the data type isses we saw earlier. But let’s just check they’re as bad as we think using df.info()
df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 929 entries, 1 to 929
Data columns (total 16 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Date 929 non-null int64
1 Address 929 non-null object
2 Bedrooms 929 non-null int64
3 Baths 929 non-null float64
4 SquareFeet 911 non-null float64
5 LotSize 741 non-null float64
6 YearBuilt 929 non-null int64
7 YearSold 929 non-null int64
8 MonthSold 929 non-null int64
9 DaySold 929 non-null int64
10 CostPerSqFt 929 non-null float64
11 OrigPrice 929 non-null int64
12 ListPrice 929 non-null int64
13 SalePrice 929 non-null int64
14 SPLPPct 929 non-null float64
15 datetime 929 non-null datetime64[ns]
dtypes: datetime64[ns](1), float64(5), int64(9), object(1)
memory usage: 123.4+ KB
pandas read most of them in sensibly, but not all of them, so let’s adjust the ones we need to:
df = df.assign(
Address=df["Address"].astype("string"),
Bedrooms=df["Bedrooms"].astype("category"),
OrigPrice=df["OrigPrice"].astype(float),
ListPrice=df["ListPrice"].astype(float),
SalePrice=df["SalePrice"].astype(float),
)
df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 929 entries, 1 to 929
Data columns (total 16 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Date 929 non-null int64
1 Address 929 non-null string
2 Bedrooms 929 non-null category
3 Baths 929 non-null float64
4 SquareFeet 911 non-null float64
5 LotSize 741 non-null float64
6 YearBuilt 929 non-null int64
7 YearSold 929 non-null int64
8 MonthSold 929 non-null int64
9 DaySold 929 non-null int64
10 CostPerSqFt 929 non-null float64
11 OrigPrice 929 non-null float64
12 ListPrice 929 non-null float64
13 SalePrice 929 non-null float64
14 SPLPPct 929 non-null float64
15 datetime 929 non-null datetime64[ns]
dtypes: category(1), datetime64[ns](1), float64(8), int64(5), string(1)
memory usage: 117.4 KB
Having to do some initial variable type cleaning is a normal and unavoidable part of data analysis, especially when reading in from a format like CSV (which does not preserve data type but has great interoperability across systems). It’s important and what we’ve seen here is a typical pattern.
There’s just one more step before starting the EDA proper. If you haven’t reached the Chapter on best practice, which talks about naming conventions, you won’t know this yet but the usual naming convention for variables and columns in Python is so-called snake case (appropriately enough). An example of a word not in snake case would be ‘ThisIsAColName’; actually that’s in Camel Case. Rendered in snake case, it would be ‘this_is_a_col_name’, which is longer but slightly easier to read. So we should really change the column names to be in snake case. Fortunately, the dataprep package has us covered here; it provides methods to translate between pretty much every naming convention that exists. Let’s change our columns to snake case, which is the default setting of clean_headers.
TODO: add this example when dataprep updated
from dataprep.clean import clean_headers
df = clean_headers(df)
Now we can start the exploratory data analysis!
3.2. EDA using pandas built-in methods¶
pandas has some great options for built-in EDA; in fact we’ve already seen one of them, df.info() which, as well as reporting datatypes and memory usage, also tells us how many observations in each column are ‘truthy’ rather than ‘falsy’, ie how many have non-null values.
3.2.1. Exploratory tables and descriptive statistics¶
A small step beyond df.info to get tables is to use df.describe() which, if you have mixed datatypes that include floats, will report some basic summary statistics:
df.describe()
| Date | Baths | SquareFeet | LotSize | YearBuilt | YearSold | MonthSold | DaySold | CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| count | 929.000000 | 929.000000 | 911.000000 | 741.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 | 929.000000 |
| mean | 18223.215285 | 1.779333 | 1583.215148 | 0.723463 | 1945.903122 | 2009.365985 | 6.831001 | 16.226050 | 83.291561 | 146047.083961 | 141399.138859 | 133203.720129 | 93.563994 |
| std | 1139.171002 | 0.743154 | 655.664966 | 2.725697 | 37.209902 | 3.124149 | 3.035790 | 9.396815 | 35.762241 | 85174.109372 | 82228.201763 | 77959.624697 | 6.953764 |
| min | 16527.000000 | 0.000000 | 640.000000 | 0.028926 | 1870.000000 | 2005.000000 | 1.000000 | 1.000000 | 0.000000 | 5990.000000 | 10500.000000 | 7000.000000 | 46.500000 |
| 25% | 17169.000000 | 1.000000 | 1150.000000 | 0.233884 | 1900.000000 | 2007.000000 | 5.000000 | 8.000000 | 60.820000 | 89900.000000 | 88000.000000 | 83000.000000 | 91.320000 |
| 50% | 18046.000000 | 1.750000 | 1440.000000 | 0.284091 | 1956.000000 | 2009.000000 | 7.000000 | 16.000000 | 82.880000 | 129900.000000 | 125000.000000 | 119340.000000 | 94.920000 |
| 75% | 19281.000000 | 2.000000 | 1833.000000 | 0.370179 | 1973.000000 | 2012.000000 | 9.000000 | 25.000000 | 105.770000 | 179000.000000 | 172000.000000 | 162500.000000 | 97.560000 |
| max | 20186.000000 | 6.000000 | 6815.000000 | 55.000000 | 2013.000000 | 2015.000000 | 12.000000 | 31.000000 | 258.790000 | 695000.000000 | 695000.000000 | 606000.000000 | 111.020000 |
Although helpful, that sure is hard to read! We can improve this by using the round() method too:
sum_table = df.describe().round(1)
sum_table
| Date | Baths | SquareFeet | LotSize | YearBuilt | YearSold | MonthSold | DaySold | CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| count | 929.0 | 929.0 | 911.0 | 741.0 | 929.0 | 929.0 | 929.0 | 929.0 | 929.0 | 929.0 | 929.0 | 929.0 | 929.0 |
| mean | 18223.2 | 1.8 | 1583.2 | 0.7 | 1945.9 | 2009.4 | 6.8 | 16.2 | 83.3 | 146047.1 | 141399.1 | 133203.7 | 93.6 |
| std | 1139.2 | 0.7 | 655.7 | 2.7 | 37.2 | 3.1 | 3.0 | 9.4 | 35.8 | 85174.1 | 82228.2 | 77959.6 | 7.0 |
| min | 16527.0 | 0.0 | 640.0 | 0.0 | 1870.0 | 2005.0 | 1.0 | 1.0 | 0.0 | 5990.0 | 10500.0 | 7000.0 | 46.5 |
| 25% | 17169.0 | 1.0 | 1150.0 | 0.2 | 1900.0 | 2007.0 | 5.0 | 8.0 | 60.8 | 89900.0 | 88000.0 | 83000.0 | 91.3 |
| 50% | 18046.0 | 1.8 | 1440.0 | 0.3 | 1956.0 | 2009.0 | 7.0 | 16.0 | 82.9 | 129900.0 | 125000.0 | 119340.0 | 94.9 |
| 75% | 19281.0 | 2.0 | 1833.0 | 0.4 | 1973.0 | 2012.0 | 9.0 | 25.0 | 105.8 | 179000.0 | 172000.0 | 162500.0 | 97.6 |
| max | 20186.0 | 6.0 | 6815.0 | 55.0 | 2013.0 | 2015.0 | 12.0 | 31.0 | 258.8 | 695000.0 | 695000.0 | 606000.0 | 111.0 |
Now, although this is very basic, let’s say you wanted to include it as a table of summary statistics in a paper. This is just a dataframe so you can export it just like you can any pandas dataframe. This includes options like .to_excel. For inclusion in a paper or report, you’re most likely to want to report it as text:
print(sum_table.to_string())
Date Baths SquareFeet LotSize YearBuilt YearSold MonthSold DaySold CostPerSqFt OrigPrice ListPrice SalePrice SPLPPct
count 929.0 929.0 911.0 741.0 929.0 929.0 929.0 929.0 929.0 929.0 929.0 929.0 929.0
mean 18223.2 1.8 1583.2 0.7 1945.9 2009.4 6.8 16.2 83.3 146047.1 141399.1 133203.7 93.6
std 1139.2 0.7 655.7 2.7 37.2 3.1 3.0 9.4 35.8 85174.1 82228.2 77959.6 7.0
min 16527.0 0.0 640.0 0.0 1870.0 2005.0 1.0 1.0 0.0 5990.0 10500.0 7000.0 46.5
25% 17169.0 1.0 1150.0 0.2 1900.0 2007.0 5.0 8.0 60.8 89900.0 88000.0 83000.0 91.3
50% 18046.0 1.8 1440.0 0.3 1956.0 2009.0 7.0 16.0 82.9 129900.0 125000.0 119340.0 94.9
75% 19281.0 2.0 1833.0 0.4 1973.0 2012.0 9.0 25.0 105.8 179000.0 172000.0 162500.0 97.6
max 20186.0 6.0 6815.0 55.0 2013.0 2015.0 12.0 31.0 258.8 695000.0 695000.0 606000.0 111.0
as Markdown:
print(sum_table.to_markdown())
| | Date | Baths | SquareFeet | LotSize | YearBuilt | YearSold | MonthSold | DaySold | CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct |
|:------|--------:|--------:|-------------:|----------:|------------:|-----------:|------------:|----------:|--------------:|------------:|------------:|------------:|----------:|
| count | 929 | 929 | 911 | 741 | 929 | 929 | 929 | 929 | 929 | 929 | 929 | 929 | 929 |
| mean | 18223.2 | 1.8 | 1583.2 | 0.7 | 1945.9 | 2009.4 | 6.8 | 16.2 | 83.3 | 146047 | 141399 | 133204 | 93.6 |
| std | 1139.2 | 0.7 | 655.7 | 2.7 | 37.2 | 3.1 | 3 | 9.4 | 35.8 | 85174.1 | 82228.2 | 77959.6 | 7 |
| min | 16527 | 0 | 640 | 0 | 1870 | 2005 | 1 | 1 | 0 | 5990 | 10500 | 7000 | 46.5 |
| 25% | 17169 | 1 | 1150 | 0.2 | 1900 | 2007 | 5 | 8 | 60.8 | 89900 | 88000 | 83000 | 91.3 |
| 50% | 18046 | 1.8 | 1440 | 0.3 | 1956 | 2009 | 7 | 16 | 82.9 | 129900 | 125000 | 119340 | 94.9 |
| 75% | 19281 | 2 | 1833 | 0.4 | 1973 | 2012 | 9 | 25 | 105.8 | 179000 | 172000 | 162500 | 97.6 |
| max | 20186 | 6 | 6815 | 55 | 2013 | 2015 | 12 | 31 | 258.8 | 695000 | 695000 | 606000 | 111 |
as HTML:
print(sum_table.head(3).to_html())
<table border="1" class="dataframe">
<thead>
<tr style="text-align: right;">
<th></th>
<th>Date</th>
<th>Baths</th>
<th>SquareFeet</th>
<th>LotSize</th>
<th>YearBuilt</th>
<th>YearSold</th>
<th>MonthSold</th>
<th>DaySold</th>
<th>CostPerSqFt</th>
<th>OrigPrice</th>
<th>ListPrice</th>
<th>SalePrice</th>
<th>SPLPPct</th>
</tr>
</thead>
<tbody>
<tr>
<th>count</th>
<td>929.0</td>
<td>929.0</td>
<td>911.0</td>
<td>741.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
<td>929.0</td>
</tr>
<tr>
<th>mean</th>
<td>18223.2</td>
<td>1.8</td>
<td>1583.2</td>
<td>0.7</td>
<td>1945.9</td>
<td>2009.4</td>
<td>6.8</td>
<td>16.2</td>
<td>83.3</td>
<td>146047.1</td>
<td>141399.1</td>
<td>133203.7</td>
<td>93.6</td>
</tr>
<tr>
<th>std</th>
<td>1139.2</td>
<td>0.7</td>
<td>655.7</td>
<td>2.7</td>
<td>37.2</td>
<td>3.1</td>
<td>3.0</td>
<td>9.4</td>
<td>35.8</td>
<td>85174.1</td>
<td>82228.2</td>
<td>77959.6</td>
<td>7.0</td>
</tr>
</tbody>
</table>
or as latex:
print(sum_table.to_latex(caption="Summary stats from EDA"))
\begin{table}
\centering
\caption{Summary stats from EDA}
\begin{tabular}{lrrrrrrrrrrrrr}
\toprule
{} & Date & Baths & SquareFeet & LotSize & YearBuilt & YearSold & MonthSold & DaySold & CostPerSqFt & OrigPrice & ListPrice & SalePrice & SPLPPct \\
\midrule
count & 929.0 & 929.0 & 911.0 & 741.0 & 929.0 & 929.0 & 929.0 & 929.0 & 929.0 & 929.0 & 929.0 & 929.0 & 929.0 \\
mean & 18223.2 & 1.8 & 1583.2 & 0.7 & 1945.9 & 2009.4 & 6.8 & 16.2 & 83.3 & 146047.1 & 141399.1 & 133203.7 & 93.6 \\
std & 1139.2 & 0.7 & 655.7 & 2.7 & 37.2 & 3.1 & 3.0 & 9.4 & 35.8 & 85174.1 & 82228.2 & 77959.6 & 7.0 \\
min & 16527.0 & 0.0 & 640.0 & 0.0 & 1870.0 & 2005.0 & 1.0 & 1.0 & 0.0 & 5990.0 & 10500.0 & 7000.0 & 46.5 \\
25\% & 17169.0 & 1.0 & 1150.0 & 0.2 & 1900.0 & 2007.0 & 5.0 & 8.0 & 60.8 & 89900.0 & 88000.0 & 83000.0 & 91.3 \\
50\% & 18046.0 & 1.8 & 1440.0 & 0.3 & 1956.0 & 2009.0 & 7.0 & 16.0 & 82.9 & 129900.0 & 125000.0 & 119340.0 & 94.9 \\
75\% & 19281.0 & 2.0 & 1833.0 & 0.4 & 1973.0 & 2012.0 & 9.0 & 25.0 & 105.8 & 179000.0 & 172000.0 & 162500.0 & 97.6 \\
max & 20186.0 & 6.0 & 6815.0 & 55.0 & 2013.0 & 2015.0 & 12.0 & 31.0 & 258.8 & 695000.0 & 695000.0 & 606000.0 & 111.0 \\
\bottomrule
\end{tabular}
\end{table}
And remember, with all of these, you can write them to file using the following structure:
open('sum_stats_table.tex', 'w').write(sum_table.to_latex(caption='Summary stats from EDA'))
Of course, the stats provided in this pre-built table are not very customised. So what do we do to get the table that we actually want? Well, the answer is to draw on the contents of the previous data chapters, particularly the introduction to data analysis. Groupbys, merges, aggregations: use all of them to produce the EDA table that you want.
If you’re exploring data, you might also want to be able to read everything clearly and see any deviations from what you’d expect quickly. pandas has some built-in functionality that styles dataframes to help you. These styles persist when you export the dataframe to, say, Excel, too.
Here’s an example that highlights some ways of styling dataframes, making use of several features such as: changing the units (lambda function), unstacking into a wider format (unstack), fill NaNs with unobtrusive strings (.fillna('-')), removing numbers after the decimal place (.style.set_precision(0)), and adding a caption (.style.set_caption).
(
df.groupby(["YearSold", "Bedrooms"])
.mean()["SalePrice"]
.apply(lambda x: x / 1e3)
.unstack()
.fillna("-")
.style.set_precision(0)
.set_caption("Sale price (thousands)")
)
| Bedrooms | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|---|---|
| YearSold | |||||||||
| 2005 | 155 | - | 82 | 112 | 141 | 174 | 205 | 81 | - |
| 2006 | 25 | - | 80 | 125 | 139 | 281 | - | - | 205 |
| 2007 | - | - | 83 | 110 | 171 | 245 | 400 | - | - |
| 2008 | 238 | 62 | 82 | 132 | 158 | 236 | 221 | - | - |
| 2009 | - | 84 | 78 | 104 | 129 | 159 | - | - | - |
| 2010 | - | - | 68 | 120 | 161 | 195 | - | - | - |
| 2011 | 128 | - | 83 | 128 | 176 | 213 | - | - | - |
| 2012 | 191 | 38 | 98 | 132 | 154 | 213 | 305 | - | - |
| 2013 | 382 | 88 | 77 | 129 | 197 | 182 | 141 | - | - |
| 2014 | 55 | 65 | 94 | 147 | 157 | 198 | - | - | - |
| 2015 | - | - | 93 | 126 | 213 | - | - | - | - |
Although a neater one than we’ve seen, this is still a drab table of numbers. The eye is not immediately drawn to it!
To remedy that, let’s take a look at another styling technique: the use of colour. Let’s say we wanted to make a table that showed a cross-tabulation between year and number of bathrooms. Naturally, we’ll use pd.crosstab but we’ll ask that the values that appear in the table (counts) be lit up with a heatmap:
pd.crosstab(df["Bedrooms"], df["YearSold"]).style.background_gradient(cmap="plasma")
| YearSold | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Bedrooms | |||||||||||
| 0 | 1 | 1 | 0 | 1 | 0 | 0 | 3 | 3 | 1 | 2 | 0 |
| 1 | 0 | 0 | 0 | 2 | 1 | 0 | 0 | 1 | 2 | 1 | 0 |
| 2 | 15 | 18 | 24 | 17 | 14 | 10 | 10 | 11 | 18 | 11 | 1 |
| 3 | 54 | 64 | 56 | 59 | 25 | 27 | 31 | 47 | 39 | 46 | 15 |
| 4 | 21 | 39 | 24 | 17 | 10 | 14 | 14 | 28 | 28 | 30 | 3 |
| 5 | 7 | 8 | 6 | 8 | 3 | 2 | 8 | 6 | 4 | 9 | 0 |
| 6 | 1 | 0 | 1 | 2 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 7 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 8 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Here are a couple of other styling tips for dataframes.
First, use bars to show ordering:
(
df.iloc[:10, -6:-1]
.style.set_precision(0)
.bar(subset=["CostPerSqFt", "SalePrice"], color="#d65f5f")
)
| CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | |
|---|---|---|---|---|---|
| 1 | 6 | 17000 | 10500 | 7000 | 67 |
| 2 | 22 | 35000 | 35000 | 27000 | 77 |
| 3 | 18 | 54000 | 47000 | 28000 | 60 |
| 4 | 26 | 65000 | 49000 | 30000 | 61 |
| 5 | 24 | 35000 | 35000 | 30750 | 88 |
| 6 | 39 | 45900 | 45900 | 42000 | 92 |
| 7 | 60 | 62500 | 56500 | 46000 | 81 |
| 8 | 43 | 54500 | 52500 | 49500 | 94 |
| 9 | 55 | 59900 | 52500 | 50000 | 95 |
| 10 | 34 | 50000 | 50000 | 50000 | 100 |
Second, use format to add informative suffixes and round numbers appropriately:
df.groupby(["Bedrooms"])[["SPLPPct"]].mean().style.format("{:.0f}%")
| SPLPPct | |
|---|---|
| Bedrooms | |
| 0 | 97% |
| 1 | 89% |
| 2 | 92% |
| 3 | 94% |
| 4 | 94% |
| 5 | 93% |
| 6 | 90% |
| 7 | 95% |
| 8 | 82% |
Third, use .hightlight_max, and similar commands, to show important entries:
df.iloc[:10, -6:-1].style.highlight_max().format("{:.0f}")
| CostPerSqFt | OrigPrice | ListPrice | SalePrice | SPLPPct | |
|---|---|---|---|---|---|
| 1 | 6 | 17000 | 10500 | 7000 | 67 |
| 2 | 22 | 35000 | 35000 | 27000 | 77 |
| 3 | 18 | 54000 | 47000 | 28000 | 60 |
| 4 | 26 | 65000 | 49000 | 30000 | 61 |
| 5 | 24 | 35000 | 35000 | 30750 | 88 |
| 6 | 39 | 45900 | 45900 | 42000 | 92 |
| 7 | 60 | 62500 | 56500 | 46000 | 81 |
| 8 | 43 | 54500 | 52500 | 49500 | 94 |
| 9 | 55 | 59900 | 52500 | 50000 | 95 |
| 10 | 34 | 50000 | 50000 | 50000 | 100 |
You can find a full set of styling commands here.
3.2.2. Exploratory plotting¶
pandas has some built-in plotting options to help you look at data quickly. These can be accessed via .plot.* or .plot(), depending on the context. Let’s make a quick .plot() of the mean quarterly nominal Sale Price over time (with extra options passed via keyword arguments):
(
df.set_index("datetime")
.groupby(pd.Grouper(freq="3M"))["SalePrice"]
.mean()
.apply(lambda x: x / 1e3)
.plot(
title="House sales in Grinnell, Iowa",
xlabel="",
ylabel="Mean nominal sale price (000s USD)",
)
);
Again, if you can get the data in the right shape, you can plot it. The same function works with multiple lines
(
df.set_index("datetime")
.groupby(pd.Grouper(freq="3M"))[["OrigPrice", "ListPrice", "SalePrice"]]
.mean()
.apply(lambda x: x / 1e3)
.plot(style=["-", ":", "-."])
);
Now let’s see some of the other quick .plot.* options.
Here’s a KDE plot. Just to show what’s possible, we’ll use the df.columnname syntax, an alternative to df['columnname'], and setting limits via keyword arguments.
df.SquareFeet.plot.kde(ylim=(0, None), xlim=(0, None));
A bar chart (use barh for horizontal orientation; rot sets rotation of labels):
df.value_counts("Bedrooms").sort_index().plot.bar(title="Counts", rot=0);
This chart shows why EDA is so important: if we are modelling residential housing, and we find that there are observations that don’t have any bedrooms, perhaps they should be excluded from the downstream analysis.
This next one, using .plot.hist, provides another instructive example of how EDA can reveal interesting issues with datasets. First, there are many more houses built in 1900 than seem plausible and, checking the documentation for the dataset, it does say that when a construction date is not known, it is entered as 1900-which explains what’s going on here. Second, some of the construction years are in the future! Is this plausible? It could be. Some houses are sold before the construction of an estate is completed; this could be the case here.
df["YearBuilt"].plot.hist(bins=30, title="Year of construction");
Boxplot:
(df[["OrigPrice", "ListPrice", "SalePrice"]].plot.box());
df["class_ln_price"] = pd.cut(np.log(df["OrigPrice"]), bins=4, precision=0)
(
df.set_index("datetime")
.groupby([pd.Grouper(freq="A"), "class_ln_price"])["class_ln_price"]
.count()
.unstack()
.plot.area(ylabel="Count")
);
Scatter plot:
df.plot.scatter(x="SquareFeet", y="SalePrice", alpha=0.7, ylim=(0, None));
Hexbin:
df.plot.hexbin(
y="ListPrice",
x="SPLPPct",
gridsize=10,
cmap="inferno_r",
C="CostPerSqFt",
sharex=False,
);
3.3. The pandas-profiling package¶
The EDA we did using the built-in pandas functions was a bit limited and user-input heavy. The pandas-profiling library aims to automate the legwork of EDA for you. It generates ‘profile’ reports from a pandas DataFrame. For each column, many statistics are computed and then relayed in an interactive HTML report.
Let’s generate a report on our dataset using the minimal=True setting (the default settings produce a lot of computationally expensive extras):
from pandas_profiling import ProfileReport
profile = ProfileReport(
df, minimal=True, title="Profiling Report: Grinnell House Sales"
)
profile.to_notebook_iframe()
This is a full on report about everything in our dataset! We can see, for instance, that we have 17 numerical variables, 0 boolean, and 4 categorical (which includes string), and 1 datetime. The overview also tells us the number of observations, that we have 1.2% of values missing across all columns, and we have zero duplicates.
The warnings page shows where pandas-profiling really shines. It flags potential issues with the data that should be taken into account in any subsequent analysis. For example, although not relevant here, the report will say if there are very unbalanced classes in a low cardinality categorical variable.
Let’s explore a couple of the warnings that have been raised. Address has high cardinality; but that’s okay, we expect most addresses to be different because the turn-over of houses is not that frequent. We also see that there are a lot of missing values for LotSize.
Now let’s dig down into a specific variable, SalePrice. You can view more about it using the ‘Toggle details’ button. We get four extra pages of detail about this variable, in addition to the headline statistics: a comprehensive page of descriptive statistics, a page with a histogram of the variable, a page with a table of common values, and a page of extreme values.
3.4. The dataprep package¶
The dataprep package offers very similar functionality to pandas-profiling; it produces an in-depth report on the input data. There isn’t much difference between them in general apart from it seems like dataprep has slightly better support for string column types (check out the details of Address below) and is a little bit richer on visualisations (it has interactive plots that you can change the axes of-look at the interactions page below).
Do note, however, that some column types can create an error-which is why we’ll pass in all but the last column of our dataframe from the previous set of examples. In this case, having a bin-category column seems to be an issue.
from dataprep.eda import create_report
report = create_report(df.iloc[:, :-1])
/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)
/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)
/Users/aet/opt/anaconda3/envs/codeforecon/lib/python3.8/site-packages/dask/dataframe/accessor.py:48: FutureWarning: The default value of regex will change from True to False in a future version.
out = getattr(getattr(obj, accessor, obj), attr)(*args, **kwargs)
from IPython.core.display import display
display(report)
Dataset Statistics
| Number of Variables | 16 |
|---|---|
| Number of Rows | 929 |
| Missing Cells | 206 |
| Missing Cells (%) | 1.4% |
| Duplicate Rows | 10 |
| Duplicate Rows (%) | 1.1% |
| Total Size in Memory | 206.1 KB |
| Average Row Size in Memory | 227.2 B |
Variable Types
| Numerical | 13 |
|---|---|
| Categorical | 2 |
| DateTime | 1 |
Date
numerical
| Distinct Count | 709 |
|---|---|
| Unique (%) | 76.3% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 18223.2153 |
| Minimum | 16527 |
| Maximum | 20186 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 16527 |
|---|---|
| 5-th Percentile | 16657.6 |
| Q1 | 17169 |
| Median | 18046 |
| Q3 | 19281 |
| 95-th Percentile | 19990.4 |
| Maximum | 20186 |
| Range | 3659 |
| IQR | 2112 |
Descriptive Statistics
| Mean | 18223.2153 |
|---|---|
| Standard Deviation | 1139.171 |
| Variance | 1.2977e+06 |
| Sum | 1.6929e+07 |
| Skewness | 0.1729 |
| Kurtosis | -1.3933 |
| Coefficient of Variation | 0.06251 |
Address
categorical
| Distinct Count | 806 |
|---|---|
| Unique (%) | 86.8% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Memory Size | 71.9 KB |
Length
| Mean | 14.2573 |
|---|---|
| Standard Deviation | 2.4748 |
| Median | 13 |
| Minimum | 8 |
| Maximum | 23 |
Sample
| 1st row | 1510 First Ave #11... |
|---|---|
| 2nd row | 1020 Center St |
| 3rd row | 918 Chatterton St |
| 4th row | 1023 & 1025 Spring... |
| 5th row | 503 2nd Ave |
Letter
| Count | 6591 |
|---|---|
| Lowercase Letter | 5013 |
| Space Separator | 2804 |
| Uppercase Letter | 1578 |
| Dash Punctuation | 13 |
| Decimal Number | 3795 |
Bedrooms
categorical
| Distinct Count | 9 |
|---|---|
| Unique (%) | 1.0% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Memory Size | 59.9 KB |
Length
| Mean | 1 |
|---|---|
| Standard Deviation | 0 |
| Median | 1 |
| Minimum | 1 |
| Maximum | 1 |
Sample
| 1st row | 2 |
|---|---|
| 2nd row | 3 |
| 3rd row | 4 |
| 4th row | 3 |
| 5th row | 3 |
Letter
| Count | 0 |
|---|---|
| Lowercase Letter | 0 |
| Space Separator | 0 |
| Uppercase Letter | 0 |
| Dash Punctuation | 0 |
| Decimal Number | 929 |
Baths
numerical
| Distinct Count | 16 |
|---|---|
| Unique (%) | 1.7% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 1.7793 |
| Minimum | 0 |
| Maximum | 6 |
| Zeros | 12 |
| Zeros (%) | 1.3% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 0 |
|---|---|
| 5-th Percentile | 1 |
| Q1 | 1 |
| Median | 1.75 |
| Q3 | 2 |
| 95-th Percentile | 3 |
| Maximum | 6 |
| Range | 6 |
| IQR | 1 |
Descriptive Statistics
| Mean | 1.7793 |
|---|---|
| Standard Deviation | 0.7432 |
| Variance | 0.5523 |
| Sum | 1653 |
| Skewness | 0.9974 |
| Kurtosis | 2.6003 |
| Coefficient of Variation | 0.4177 |
SquareFeet
numerical
| Distinct Count | 526 |
|---|---|
| Unique (%) | 57.7% |
| Missing | 18 |
| Missing (%) | 1.9% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.2 KB |
| Mean | 1583.2151 |
| Minimum | 640 |
| Maximum | 6815 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 640 |
|---|---|
| 5-th Percentile | 884 |
| Q1 | 1150 |
| Median | 1440 |
| Q3 | 1833 |
| 95-th Percentile | 2855 |
| Maximum | 6815 |
| Range | 6175 |
| IQR | 683 |
Descriptive Statistics
| Mean | 1583.2151 |
|---|---|
| Standard Deviation | 655.665 |
| Variance | 429896.5471 |
| Sum | 1.4423e+06 |
| Skewness | 2.1205 |
| Kurtosis | 8.0646 |
| Coefficient of Variation | 0.4141 |
LotSize
numerical
| Distinct Count | 398 |
|---|---|
| Unique (%) | 53.7% |
| Missing | 188 |
| Missing (%) | 20.2% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 11.6 KB |
| Mean | 0.7235 |
| Minimum | 0.02893 |
| Maximum | 55 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 0.02893 |
|---|---|
| 5-th Percentile | 0.14 |
| Q1 | 0.2339 |
| Median | 0.2841 |
| Q3 | 0.3702 |
| 95-th Percentile | 1.82 |
| Maximum | 55 |
| Range | 54.9711 |
| IQR | 0.1363 |
Descriptive Statistics
| Mean | 0.7235 |
|---|---|
| Standard Deviation | 2.7257 |
| Variance | 7.4294 |
| Sum | 536.0862 |
| Skewness | 13.0898 |
| Kurtosis | 224.9221 |
| Coefficient of Variation | 3.7676 |
YearBuilt
numerical
| Distinct Count | 101 |
|---|---|
| Unique (%) | 10.9% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 1945.9031 |
| Minimum | 1870 |
| Maximum | 2013 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 1870 |
|---|---|
| 5-th Percentile | 1900 |
| Q1 | 1900 |
| Median | 1956 |
| Q3 | 1973 |
| 95-th Percentile | 2001.6 |
| Maximum | 2013 |
| Range | 143 |
| IQR | 73 |
Descriptive Statistics
| Mean | 1945.9031 |
|---|---|
| Standard Deviation | 37.2099 |
| Variance | 1384.5768 |
| Sum | 1.8077e+06 |
| Skewness | -0.1125 |
| Kurtosis | -1.3947 |
| Coefficient of Variation | 0.01912 |
YearSold
numerical
| Distinct Count | 11 |
|---|---|
| Unique (%) | 1.2% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 2009.366 |
| Minimum | 2005 |
| Maximum | 2015 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 2005 |
|---|---|
| 5-th Percentile | 2005 |
| Q1 | 2007 |
| Median | 2009 |
| Q3 | 2012 |
| 95-th Percentile | 2014 |
| Maximum | 2015 |
| Range | 10 |
| IQR | 5 |
Descriptive Statistics
| Mean | 2009.366 |
|---|---|
| Standard Deviation | 3.1241 |
| Variance | 9.7603 |
| Sum | 1.8667e+06 |
| Skewness | 0.1688 |
| Kurtosis | -1.3769 |
| Coefficient of Variation | 0.001555 |
MonthSold
numerical
| Distinct Count | 12 |
|---|---|
| Unique (%) | 1.3% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 6.831 |
| Minimum | 1 |
| Maximum | 12 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 1 |
|---|---|
| 5-th Percentile | 2 |
| Q1 | 5 |
| Median | 7 |
| Q3 | 9 |
| 95-th Percentile | 12 |
| Maximum | 12 |
| Range | 11 |
| IQR | 4 |
Descriptive Statistics
| Mean | 6.831 |
|---|---|
| Standard Deviation | 3.0358 |
| Variance | 9.216 |
| Sum | 6346 |
| Skewness | -0.07994 |
| Kurtosis | -0.9231 |
| Coefficient of Variation | 0.4444 |
DaySold
numerical
| Distinct Count | 31 |
|---|---|
| Unique (%) | 3.3% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 16.226 |
| Minimum | 1 |
| Maximum | 31 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 1 |
|---|---|
| 5-th Percentile | 1 |
| Q1 | 8 |
| Median | 16 |
| Q3 | 25 |
| 95-th Percentile | 30 |
| Maximum | 31 |
| Range | 30 |
| IQR | 17 |
Descriptive Statistics
| Mean | 16.226 |
|---|---|
| Standard Deviation | 9.3968 |
| Variance | 88.3001 |
| Sum | 15074 |
| Skewness | -0.09188 |
| Kurtosis | -1.2452 |
| Coefficient of Variation | 0.5791 |
CostPerSqFt
numerical
| Distinct Count | 856 |
|---|---|
| Unique (%) | 92.1% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 83.2916 |
| Minimum | 0 |
| Maximum | 258.79 |
| Zeros | 18 |
| Zeros (%) | 1.9% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 0 |
|---|---|
| 5-th Percentile | 21.934 |
| Q1 | 60.82 |
| Median | 82.88 |
| Q3 | 105.77 |
| 95-th Percentile | 143.968 |
| Maximum | 258.79 |
| Range | 258.79 |
| IQR | 44.95 |
Descriptive Statistics
| Mean | 83.2916 |
|---|---|
| Standard Deviation | 35.7622 |
| Variance | 1278.9379 |
| Sum | 77377.86 |
| Skewness | 0.1412 |
| Kurtosis | 0.4294 |
| Coefficient of Variation | 0.4294 |
OrigPrice
numerical
| Distinct Count | 385 |
|---|---|
| Unique (%) | 41.4% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 146047.084 |
| Minimum | 5990 |
| Maximum | 695000 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 5990 |
|---|---|
| 5-th Percentile | 49900 |
| Q1 | 89900 |
| Median | 129900 |
| Q3 | 179000 |
| 95-th Percentile | 313000 |
| Maximum | 695000 |
| Range | 689010 |
| IQR | 89100 |
Descriptive Statistics
| Mean | 146047.084 |
|---|---|
| Standard Deviation | 85174.1094 |
| Variance | 7.2546e+09 |
| Sum | 1.3568e+08 |
| Skewness | 1.8714 |
| Kurtosis | 5.8245 |
| Coefficient of Variation | 0.5832 |
ListPrice
numerical
| Distinct Count | 373 |
|---|---|
| Unique (%) | 40.2% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 141399.1389 |
| Minimum | 10500 |
| Maximum | 695000 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 10500 |
|---|---|
| 5-th Percentile | 46360 |
| Q1 | 88000 |
| Median | 125000 |
| Q3 | 172000 |
| 95-th Percentile | 292800 |
| Maximum | 695000 |
| Range | 684500 |
| IQR | 84000 |
Descriptive Statistics
| Mean | 141399.1389 |
|---|---|
| Standard Deviation | 82228.2018 |
| Variance | 6.7615e+09 |
| Sum | 1.3136e+08 |
| Skewness | 1.8469 |
| Kurtosis | 5.8706 |
| Coefficient of Variation | 0.5815 |
SalePrice
numerical
| Distinct Count | 405 |
|---|---|
| Unique (%) | 43.6% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 133203.7201 |
| Minimum | 7000 |
| Maximum | 606000 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 7000 |
|---|---|
| 5-th Percentile | 37000 |
| Q1 | 83000 |
| Median | 119340 |
| Q3 | 162500 |
| 95-th Percentile | 281900 |
| Maximum | 606000 |
| Range | 599000 |
| IQR | 79500 |
Descriptive Statistics
| Mean | 133203.7201 |
|---|---|
| Standard Deviation | 77959.6247 |
| Variance | 6.0777e+09 |
| Sum | 1.2375e+08 |
| Skewness | 1.6968 |
| Kurtosis | 4.8016 |
| Coefficient of Variation | 0.5853 |
SPLPPct
numerical
| Distinct Count | 593 |
|---|---|
| Unique (%) | 63.8% |
| Missing | 0 |
| Missing (%) | 0.0% |
| Infinite | 0 |
| Infinite (%) | 0.0% |
| Memory Size | 14.5 KB |
| Mean | 93.564 |
| Minimum | 46.5 |
| Maximum | 111.02 |
| Zeros | 0 |
| Zeros (%) | 0.0% |
| Negatives | 0 |
| Negatives (%) | 0.0% |
Quantile Statistics
| Minimum | 46.5 |
|---|---|
| 5-th Percentile | 82.13 |
| Q1 | 91.32 |
| Median | 94.92 |
| Q3 | 97.56 |
| 95-th Percentile | 100 |
| Maximum | 111.02 |
| Range | 64.52 |
| IQR | 6.24 |
Descriptive Statistics
| Mean | 93.564 |
|---|---|
| Standard Deviation | 6.9538 |
| Variance | 48.3548 |
| Sum | 86920.95 |
| Skewness | -2.5043 |
| Kurtosis | 10.8721 |
| Coefficient of Variation | 0.07432 |
3.5. Review¶
That’s it for this very brief tour of exploratory data analysis with code. You should know now how to do simple analysis of pandas dataframes using techniques from other chapters to create summary statistics, using styling of tables of summary statistics, and using the quick visualisation options that are built-in to pandas. You should also know where to go for more in-depth, heavy duty exploratory data analysis reports, namely pandas-profiling and dataprep.